import 'package:development_assistant/config/db_config.dart';
import 'package:development_assistant/model/db_info.dart';
import 'package:get/get.dart';
import 'package:sqflite/sqflite.dart';

import '../model/column_info.dart';
import '../model/table_info.dart';

class DatabaseService{

  Database database=DBConfig.getDatabase();
  String dbTable="db_info";
  String tabTable="table_info";
  String columnTable="column_info";

  Future addDB(DBInfo dbInfo,List<Map<String,Object?>> tableList,List<List<Map<String,Object?>>> columnList) async{
    var dbID=await database.insert(dbTable, dbInfo.toMap());
    var tableNum=0;
    for(Map<String,Object?> tableInfo in tableList){
      tableInfo['db_id']=dbID;
      var tableID=await database.insert("table_info", tableInfo);
      for(Map<String,Object?> columnInfo in columnList[tableNum]){
        columnInfo["table_id"]=tableID;
        await database.insert(columnTable, columnInfo);
      }
      tableNum++;
    }
  }

  Future getDBList() async{
    var databaseList=await database.query(dbTable);
    var dbList=[].obs;
    var tbList=[].obs;
    var colList=[].obs;
    for(var i=0;i<databaseList.length;i++){
      dbList.add(databaseList[i]);
      var tableList=await database.query(tabTable,where: "db_id=?",whereArgs: [databaseList[i]['db_id']]);
      tbList.add(tableList.obs);
      for(var j=0;j<tableList.length;j++){
        var columnList=await database.query(columnTable,where: "table_id=?",whereArgs: [tableList[j]['table_id']]);
        colList.add(columnList.obs);
      }
    }
    
    return {
      "dbList":dbList,
      "tbList":tbList,
      "colList":colList
    };
  }

  Future deleteDatabase(int dbID) async{
    await database.delete(dbTable,where: "db_id=?",whereArgs: [dbID]);
    var delTable=await database.query(tabTable,where:"db_id=?",whereArgs: [dbID]);
    for (var table in delTable){
      await database.delete(columnTable,where: "table_id=?",whereArgs: [table["table_id"]]);
    }
    await database.delete(tabTable,where:"db_id=?",whereArgs: [dbID]);

  }

  Future updateDatabase(DBInfo dbInfo) async{
    await database.update(dbTable, dbInfo.toMap(),where: "db_id=?",whereArgs: [dbInfo.dbID]);
  }


  Future addTable(TableInfo tableInfo,List<ColumnInfo> columns) async{
    var tableID=await database.insert(tabTable, tableInfo.toMap());
    for(ColumnInfo column in columns){
      column.tableID=tableID;
      await database.insert(columnTable,column.toMap());
    }
  }

  Future deleteTable(int tableID) async{
    await database.delete(tabTable,where:"table_id=?",whereArgs: [tableID]);
    await database.delete(columnTable,where: "table_id=?",whereArgs: [tableID]);
  }

  Future updateTable(TableInfo tableInfo,List<ColumnInfo> columnInfo) async{
    await database.update(tabTable, tableInfo.toMap(),where: "table_id=?",whereArgs: [tableInfo.tableID]);
    await database.delete(columnTable,where: "table_id=?",whereArgs: [tableInfo.tableID]);
    for(var column in columnInfo){
      await database.insert(columnTable, column.toMap());
    }
  }

}